﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BIW;

namespace yunbo.Repositorys.BIWRepositorys
{
    public class Wbd_kqRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public FYwbd_kq GetfYwbd_Kq(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select q.kq_id,q.kq_code,q.kq_name,q.kq_mj,q.kq_ssck,c.ck_name,q.kq_kwsl,q.bz,q.kq_zt
from[dbo].[wbd_kq] q
inner join [dbo].[wbd_ck] c on q.kq_ssck = c.ck_code
 WHERE  q.is_deleted = 1
                                ORDER BY [kq_id]
                                OFFSET ({PageNumber}-1)*{PageSize} ROWS
                                FETCH NEXT {PageSize} ROWS ONLY;";
                List<Wbd_kq> wbd_Kqs = connection.Query<Wbd_kq>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[wbd_kq]";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYwbd_kq fYwbd_Kq = new FYwbd_kq()
                {
                    wbd_Kqs = wbd_Kqs,
                    zon = zon,
                };
                return fYwbd_Kq;
            }

        }
        public int AddWbd_kq(Wbd_kq wbd_Kq)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"insert into [dbo].[wbd_kq](kq_code, kq_name, kq_zt, bz, is_deleted, create_by, create_time , kq_mj, kq_ssck, kq_kwsl)
                                values('{wbd_Kq.kq_code}','{wbd_Kq.kq_name}','{wbd_Kq.kq_zt}','{wbd_Kq.bz}','{wbd_Kq.is_deleted}','{wbd_Kq.create_by}','{wbd_Kq.create_time}','{wbd_Kq.kq_mj}','{wbd_Kq.kq_ssck}','{wbd_Kq.kq_kwsl}') ";
                int kq = connection.Execute(sql);
                return kq;
            }
        }
        public int UpdateWbd_kq(Wbd_kq kqs)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"update [dbo].[wbd_kq] set kq_code='{kqs.kq_code}', kq_name='{kqs.kq_name}',  bz='{kqs.bz}',kq_mj='{kqs.kq_mj}',kq_ssck='{kqs.kq_ssck}', kq_kwsl='{kqs.kq_kwsl}', update_by='{kqs.update_by}', update_time='{kqs.update_time}' where kq_id='{kqs.kq_id}';";
                int kq = connection.Execute(sql);
                return kq;
            }
        }
        public int UpIsDeleted(int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wbd_kq] set is_deleted=0 where kq_id='{id}'";
                int kq = connection.Execute(sql);
                return kq;
            }
        }
        public List<Wbd_kq> Getcode(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"select [kq_code],[kq_name] from [dbo].[wbd_kq]
                           where [kq_ssck]='{code}'";
                List<Wbd_kq> cd=connection.Query<Wbd_kq>(sql).ToList();
                return cd;
            }
        }
        public int UpZt(int id, int zt)
        {
            using (SqlConnection connection = new SqlConnection(data) )
            {
                string sql = $"update [dbo].[wbd_kq] set kq_zt='{zt}' where kq_id='{id}'";
                int kq=connection.Execute(sql);
                return kq;
            }
        }
       
    }
}
